import pymysql,xlrd,urllib.request,csv
'''本文件读取股票列表，抓交易数据'''

#连接数据库
conn=pymysql.connect(host='192.168.182.128',port=3306,user='root',
                     passwd='123456',db='stock_trans',charset='utf8')
cursor=conn.cursor()
#读取股票列表 xls数据，并插入数据库中
# data=xlrd.open_workbook('股票列表.xls')
# table=data.sheet_by_name('Sheet1')
# sqlstr=''
# for i in range(1,table.nrows):
#     id = table.row_values(i)[0]
#     stock_code=int(table.row_values(i)[1])
#     stock_name=str(table.row_values(i)[2])
#     sqlstr='insert into stocks_list (id,stock_code,stock_name) values ("{}","{:0>6d}","{}")'.format(id,stock_code,stock_name)
#     row=cursor.execute(sqlstr)
#     if row == 0:
#         print('插入失败')

# 抓取每日交易数据
sqlstr='select stock_code from stocks_list'
cursor.execute(sqlstr)
rows=cursor.fetchall()
start_date='20150601'
end_date='20200314'
# for row in rows:
#     url='http://quotes.money.163.com/service/chddata.html?code=0{}&start={}&end={}' \
#         '&fields=TCLOSE;HIGH;LOW;TOPEN;LCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAP' \
#         ''.format(row[0],start_date,end_date)
#     print(url)
#     u = urllib.request.urlopen(url)
#     file_name='dairy_trans_data/{}.csv'.format(row[0])
#     f = open(file_name, 'wb')
#     block_sz = 8192
#     while True:
#         buffer = u.read(block_sz)
#         if not buffer:
#             break
#         f.write(buffer)
#     f.close()

#读取csv文件，写入数据库
for row in rows:
    file_name = 'dairy_trans_data/{}.csv'.format(row[0])
    with open(file_name,'r') as f:
        reader = csv.reader(f)
        i=0 #用于跳过第一行
        for row in reader:
            if i==0:
                i+=1
                continue
            # ['2020-03-13', "'600000", '浦发银行', '10.7', '10.73', '10.28', '10.4', '10.64', '0.06', '0.5639', '0.2044',
            #  '57457109', '603756305.0', '3.14067260248e+11', '3.00710273719e+11']
            riQi=row[0]
            guPiaoCode=row[1][1:]
            guPiaoName=row[2]
            shouPan=row[3]
            zuiGao=row[4]
            zuiDi=row[5]
            kaiPan=row[6]
            sqlstr='insert into daily_trans (riQi,guPiaoCode,guPiaoName,shouPan,zuiGao,zuiDi,kaiPan) ' \
                   'values ("{}","{}","{}","{}","{}","{}","{}")'.format(
                riQi,guPiaoCode,guPiaoName,shouPan,zuiGao,zuiDi,kaiPan)
            cursor.execute(sqlstr)
        conn.commit()

#分别查询近30天，60天，90天，120天，

conn.commit()
conn.close()



